﻿using BookModel;
using System.Data.SqlClient;


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DAL
{
    //读者信息表
    public class ReaderInfoDAL
    {

        #region 查询所有读者信息
        public List<ReaderInfo> GetReaderInfo1()
        {
            string sql = "select *from ReaderInfo";
            List<ReaderInfo> LRI = new List<ReaderInfo>();
            if (SQLHelp.OpenConn())
            {
                SqlDataReader sdr = SQLHelp.ExecReader(sql);
                if (sdr!=null)
                {
                    while (sdr.Read())
                    {
                        LRI.Add(new ReaderInfo
                            {
                                ReaderID = (int)sdr["ReaderID"],
                                ReaderName = sdr["ReaderName"] as string,
                                ReaderSex = sdr["ReaderSex"] as string,
                                ReaderCard = sdr["ReaderCard"] as string,
                                ReaderPhone = sdr["ReaderPhone"] as string,
                                ReaderAge = (int)sdr["ReaderAge"],
                                ReaderopenDate = Convert.ToDateTime(sdr["ReaderopenDate"]),
                                ReaderCancelDate = Convert.ToDateTime(sdr["ReaderCancelDate"]),
                                Readeremark = sdr["Readeremark"] as string,   

                            });
                    }
                }
                SQLHelp.CloseConn();
            }
            return LRI;
        }
        #endregion
         public List<ReaderInfo> GetRead()
        {
            List<ReaderInfo> list = new List<ReaderInfo>();
            DataTable dt = new DataTable();
            dt = SQLHelp.FillTable("select * from [ReaderInfo]");
            if ((dt != null) && (dt.Rows.Count > 0))
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ReaderInfo read = new ReaderInfo();
                    read.ReaderID = (int)dr["ReaderID"];
                    read.ReaderName = dr["ReaderName"] as string;
                    read.ReaderSex = dr["ReaderSex"] as string;
                    read.ReaderCard = dr["ReaderCard"] as string;
                    read.ReaderPhone = dr["ReaderPhone"] as string;
                    read.ReaderAge = (int)dr["ReaderAge"];
                    read.ReaderopenDate = Convert.ToDateTime(dr["ReaderopenDate"]);
                    read.ReaderCancelDate = Convert.ToDateTime(dr["ReaderCancelDate"]);
                    read.Readeremark = dr["Readeremark"] as string;

                    list.Add(read);
                }
            }
            return list;
        }
        public string  ADDReadInfo(ReaderInfo read)
        {
            string sql = "insert into [ReaderInfo] values('" + read.ReaderName + "','" + read.ReaderSex + "','" + read.ReaderCard + "','" + read.ReaderPhone + "','" + read.ReaderAge + "',getdate(),getdate(),'" + read.Readeremark + "')";
            int count = SQLHelp.ExecQuery(sql);

            //判断执行结果
            switch (count)
            {
                case -2:
                    sql = "数据库连接异常！请联系您的管理员！";
                    break;
                case -1:
                    sql = "数据操作异常！请检查您输入的数据！";
                    break;
                case 0:
                    sql = "没有添加任何数据！请检查您输入的数据！";
                    break;
                default:
                    sql = "注册成功" + count + "条数据！";
                    break;
            }

            return sql;
        }
        public string UpdateRead(int ReaderID)
        {
            //根据备注修改是否注销
            string sql = @"update ReaderInfo set Readeremark='已注销' where ReaderID='" + ReaderID + "'";
        
            int count = SQLHelp.ExecQuery(sql);
            switch (count)
            {
                case -2:
                   sql= "数据库连接异常！请联系您的管理员！";
                    break;
                case -1:
                    sql= "数据操作异常！请检查您输入的数据！";
                    break;
                case 0:
                    sql= "没有添加任何数据！请检查您输入的数据！";
                    break;
                default:
                    sql = "添加成功"+count+"条数据";
                    break;
            }
            return sql;
        }
        //查询所有者
        public List<ReaderInfo> selectBook()
        {
            List<ReaderInfo> list = new List<ReaderInfo>();
            string sql = "select * from ReaderInfo";
            if (SQLHelp.OpenConn())
            {
                SqlDataReader sdr = SQLHelp.ExecReader(sql);
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        list.Add(new ReaderInfo
                        {
                            ReaderID = (int)sdr[0],
                            ReaderName = sdr[1] as string,
                            ReaderSex = sdr[2] as string,
                            ReaderCard = sdr[3] as string,
                            ReaderPhone = sdr[4] as string,
                            ReaderAge = (int)sdr[5],
                            ReaderopenDate = Convert.ToDateTime(sdr[6]),
                            ReaderCancelDate = Convert.ToDateTime(sdr[7]),
                            Readeremark = sdr[8] as string
                        });
                    }
                }
                SQLHelp.CloseConn();
            }
            return list;
        }
    }
}
